#!/bin/bash

# Migration script to convert database from probes to trodes
# This script will backup the current database and then apply the migration

set -e  # Exit on any error

DB_PATH="monitor/data/monitor.db"
BACKUP_PATH="monitor/data/monitor.db.backup.$(date +%Y%m%d_%H%M%S)"
MIGRATION_SCRIPT="migrate_to_trodes.sql"

echo "=== Database Migration: Probes to Trodes ==="
echo "Database: $DB_PATH"
echo "Backup: $BACKUP_PATH"
echo ""

# Check if database exists
if [ ! -f "$DB_PATH" ]; then
    echo "Error: Database file not found at $DB_PATH"
    exit 1
fi

# Create backup
echo "Creating backup..."
cp "$DB_PATH" "$BACKUP_PATH"
echo "Backup created: $BACKUP_PATH"
echo ""

# Show current schema (before migration)
echo "Current schema (before migration):"
sqlite3 "$DB_PATH" ".schema" | grep -E "(CREATE TABLE|CREATE INDEX|CREATE VIEW)" | head -10
echo ""

# Show current data counts
echo "Current data counts:"
echo "Probes: $(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM probes;" 2>/dev/null || echo "0")"
echo "Metrics: $(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM metrics;" 2>/dev/null || echo "0")"
echo ""

# Confirm migration
read -p "Are you sure you want to proceed with the migration? This will delete all existing data. (y/N): " -n 1 -r
echo
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
    echo "Migration cancelled."
    exit 0
fi

# Run migration
echo "Running migration..."
sqlite3 "$DB_PATH" < "$MIGRATION_SCRIPT"
echo "Migration completed!"
echo ""

# Verify new schema
echo "New schema (after migration):"
sqlite3 "$DB_PATH" ".schema" | grep -E "(CREATE TABLE|CREATE INDEX|CREATE VIEW)" | head -10
echo ""

# Show new data counts
echo "New data counts:"
echo "Trodes: $(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM trodes;" 2>/dev/null || echo "0")"
echo "Metrics: $(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM metrics;" 2>/dev/null || echo "0")"
echo ""

echo "=== Migration completed successfully! ==="
echo "Backup saved at: $BACKUP_PATH"
echo "You can restore from backup if needed with: cp $BACKUP_PATH $DB_PATH" 